using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Emlyn.Common.Data.SqlServer.SqlManagers
{
    public class SqlManager
    {
        [ThreadStatic]
        private static List<SqlManagerConnection> _managerConnections;

        private static void Initialise()
        {
            if (_managerConnections == null)
                _managerConnections = new List<SqlManagerConnection>();
        }

        private static void Validate()
        {
            if (_managerConnections == null)
                throw new ApplicationException("No database connection has been set up");
        }

        public static void AddConnection(string aConnectionString)
        {
            Initialise();
            _managerConnections.Add(new SqlManagerConnection(aConnectionString));
        }

        internal static SqlManagerConnection GetManagerConnection(int index)
        {
            Validate();
            return _managerConnections[index];
        }

        public static DataSet GetDataSet(string aSQL)
        {
            return GetDataSet(aSQL, null, 0);
        }

        public static DataSet GetDataSet(string aSQL, List<SqlParameter> aParams)
        {
            return GetDataSet(aSQL, aParams, 0);
        }
        
        public static DataSet GetDataSet(string aSQL, List<SqlParameter> aParams, int aConnectionIndex)
        {
            Validate();

            SqlConnection conn = null;
            SqlDataAdapter adapter = null;
            DataSet retval = null;

            using (SqlManagerScope lscope = new SqlManagerScope(aConnectionIndex))
            {
                conn = lscope.Connection;

                adapter = new SqlDataAdapter(aSQL, conn);

                adapter.SelectCommand.Transaction = lscope.Transaction;

                if (aParams != null)
                {
                    foreach (SqlParameter lParam in aParams)
                    {
                        adapter.SelectCommand.Parameters.Add(lParam);
                    }
                }

                retval = new DataSet();

                adapter.Fill(retval);

                lscope.Commit();
            }

            return retval;
        }

        public static int Execute(string aSQL)
        {
            return Execute(aSQL, null, 0);
        }

        public static int Execute(string aSQL, List<SqlParameter> aParams)
        {
            return Execute(aSQL, aParams, 0);
        }

        public static int Execute(string aSQL, List<SqlParameter> aParams, int aConnectionIndex)
        {
            Validate();

            SqlConnection conn = null;
            SqlCommand cmd = null;
            int retval = -1;

            using (SqlManagerScope lscope = new SqlManagerScope(aConnectionIndex))
            {
                conn = lscope.Connection;

                cmd = new SqlCommand(aSQL, conn);
                cmd.Transaction = lscope.Transaction;

                if (aParams != null)
                {
                    foreach (SqlParameter lParam in aParams)
                    {
                        cmd.Parameters.Add(lParam);
                    }
                }

                retval = cmd.ExecuteNonQuery();

                lscope.Commit();
            }

            return retval;
        }

        public static bool TableExists(string aTableName)
        {
            return TableExists(aTableName, 0);
        }

        public static bool TableExists(string aTableName, int aConnectionIndex)
        {
            bool retval = false;

            string strSQL =
                "SELECT 1 from sysobjects where xtype='U' and Name=@Name";

            SqlParameter lparam = new SqlParameter("@Name", aTableName);
            List<SqlParameter> lparams = new List<SqlParameter>();
            lparams.Add(lparam);

            using (SqlManagerScope lscope = new SqlManagerScope(aConnectionIndex))
            {
                using (SqlDataReader reader = lscope.GetReader(strSQL, lparams))
                {
                    retval = reader.Read();
                }
            }

            return retval;
        }
    }
}
